package org.example.report_forms.controller;

import jakarta.annotation.Resource;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.example.report_forms.pojo.Product;
import org.example.report_forms.pojo.User;
import org.example.report_forms.service.ProductService;
import org.example.report_forms.service.UserService;
import org.example.report_forms.utils.ExcelExportUtils;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @ClassName TestController
 * @Author liupanpan
 * @Date 2025/1/9
 * @Description
 */
@RestController
@RequestMapping("/user")
public class TestController {
    @Resource
    ProductService productService;

    @Resource
    UserService userService;


    @GetMapping("/findPage")
    public List<User>  findPage(){
        return userService.list();
    }
    /*
     * 读取excel工作簿内容
     * */
    @PostMapping("/uploadExcel")
    public Boolean uploadExcel(MultipartFile file) throws IOException {

        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
        //获取工作表有效数据行索引
        int lastRowIndex = sheet.getLastRowNum();
        /*创建集合暂存产品数据，防止每次循环访问数据库，减少开销*/
        List<Product> products = new ArrayList<>();
        for (int i = 1; i <= lastRowIndex; i++) {
            Row row = sheet.getRow(i);
            String model = row.getCell(0).getStringCellValue();
            String code = null;
            /*code 有可能为 字符串或数值 类型, 需要处理*/
            try {
                code = row.getCell(1).getStringCellValue();
            } catch (Exception e) {
                code = row.getCell(1).getNumericCellValue()+"";
            }
            /*将double转换为 integer*/
            Integer price = (int) row.getCell(2).getNumericCellValue();
            Date date = row.getCell(3).getDateCellValue();
            Product product = Product.builder()
                    .model(model)
                    .code(code)
                    .price(price)
                    .createTime(date)
                    .build();
            /*存入product实体*/
            products.add(product);
        }
        return productService.saveBatch(products);
    }

    /**
     * 导出为excel------自己设置格式
     * @param
     * @return
     * @throws IOException
     */
    @RequestMapping("/downLoadXlsxByPoi1")
    public void downloadsExcelByPOI1(HttpServletResponse response) throws IOException {
        /*创建工作簿-工作表-行-单元格，设置标题行，循环遍历，在工作表中放入数据*/
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("产品数据");
        /*设置样式*/

        //大标题
        CellStyle bigTitleCellStyle = workbook.createCellStyle();
        //设置边框
        bigTitleCellStyle.setBorderBottom(BorderStyle.THIN);
        bigTitleCellStyle.setBorderLeft(BorderStyle.THIN);
        bigTitleCellStyle.setBorderRight(BorderStyle.THIN);
        bigTitleCellStyle.setBorderTop(BorderStyle.THIN);
        //对其方式
        bigTitleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        bigTitleCellStyle.setAlignment(HorizontalAlignment.CENTER);

        //设置字体样式，大小
        Font font = workbook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 18);
        bigTitleCellStyle.setFont(font);

        Row rowTitle = sheet.createRow(0);
        //设置行高
        rowTitle.setHeightInPoints(42);
        //设置列宽
        sheet.setColumnWidth(0,18*255);
        sheet.setColumnWidth(1,15*255);
        sheet.setColumnWidth(2,13*255);
        sheet.setColumnWidth(3,18*255);
        for (int i = 0; i < 4; i++) {
            Cell cell = rowTitle.createCell(i);
            cell.setCellStyle(bigTitleCellStyle);
        }
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        //单元格赋值
        sheet.getRow(0).getCell(0).setCellValue("产品数据");

        //样式
        CellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.cloneStyleFrom(bigTitleCellStyle);
        //设置字体样式，大小
        Font font2 = workbook.createFont();
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 12);
        font2.setBold(true);
        titleCellStyle.setFont(font2);

        String[] title = new String[]{"型号","编号","价格","创建时间"};
        Cell cell = null;

        Row row = sheet.createRow(1);
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(titleCellStyle);
        }
        //放入数据
        List<Product> products = productService.list();
        //样式
        CellStyle contentCellStyle = workbook.createCellStyle();
        contentCellStyle.cloneStyleFrom(bigTitleCellStyle);
        //设置字体样式，大小
        Font font1 = workbook.createFont();
        font1.setFontName("宋体");
        font1.setFontHeightInPoints((short) 11);
        contentCellStyle.setFont(font1);
        contentCellStyle.setAlignment(HorizontalAlignment.LEFT);
        //从第二行开始放数据
        int startRow = 2;
        Row row1 = null;
        for (Product product : products) {
            row1 = sheet.createRow(startRow);
            Cell cell1 = row1.createCell(0);
            cell1.setCellStyle(contentCellStyle);
            cell1.setCellValue(product.getModel());

            Cell cell2 = row1.createCell(1);
            cell2.setCellStyle(contentCellStyle);
            cell2.setCellValue(product.getCode());

            Cell cell3 = row1.createCell(2);
            cell3.setCellStyle(contentCellStyle);
            cell3.setCellValue(product.getPrice());
            //处理输出时间格式
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Cell cell4 = row1.createCell(3);
            cell4.setCellValue(sdf.format(product.getCreateTime()));
            cell4.setCellStyle(contentCellStyle);
            startRow++;
        }
        //导出的文件名称
        String fileName = "产品数据.xlsx";
        response.setHeader( "Content-Disposition", "attachment;filename="  + new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(response.getOutputStream());
    }



    /**
     * 导出为excel------使用模板
     * @param
     * @return
     * @throws IOException
     */
    @RequestMapping("/downLoadXlsxByPoi2")
    public void downloadsExcelByPOI2(HttpServletResponse response) throws IOException, InvalidFormatException {
        /*
        获取模版
        循环遍历，在工作表中放入数据
        导出文件
        */
        //获取模版
        //用于获取类路径下的资源文件的URL
        File path = new File(ResourceUtils.getURL("classpath:").getPath());
        File file = new File(path, "/excel_template/userList.xlsx");
        Workbook workbook = new XSSFWorkbook(file);

        //放入数据
        List<Product> products = productService.list();
        //从第二行开始放数据
        int startRow = 2;
        Row row1 = null;
        Sheet sheet = workbook.getSheetAt(0);
        //获取内容的单元格样式
        CellStyle cellStyle = workbook.getSheetAt(1).getRow(0).getCell(0).getCellStyle();
        for (Product product : products) {
            row1 = sheet.createRow(startRow);
            Cell cell1 = row1.createCell(0);
            cell1.setCellValue(product.getModel());
            cell1.setCellStyle(cellStyle);

            Cell cell2 = row1.createCell(1);
            cell2.setCellValue(product.getCode());
            cell2.setCellStyle(cellStyle);

            Cell cell3 = row1.createCell(2);
            cell3.setCellValue(product.getPrice());
            cell3.setCellStyle(cellStyle);
            //处理输出时间格式
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Cell cell4 = row1.createCell(3);
            cell4.setCellValue(sdf.format(product.getCreateTime()));
            cell4.setCellStyle(cellStyle);

            startRow++;
        }
        //删除设置内容样式的工作表
        workbook.removeSheetAt(1);
        //导出的文件名称
        String fileName = "产品数据.xlsx";
        response.setHeader( "Content-Disposition", "attachment;filename="  + new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(response.getOutputStream());
    }

    /*
    * 根据模版填充数据，再导出为excel ---------- 使用自定义导出引擎实现
    * */
    @RequestMapping("/download")
    public void downloadsExcel(Integer id,HttpServletResponse response) throws IOException, InvalidFormatException, IllegalAccessException {
        /*
        获取模版
        循环遍历，在工作表中放入数据
        导出文件
        */
        //获取模版
        //用于获取类路径下的资源文件的URL
        File path = new File(ResourceUtils.getURL("classpath:").getPath());
        File file = new File(path, "/excel_template/userInfo2.xlsx");
        Workbook workbook = new XSSFWorkbook(file);

        //查询数据库表中某个用户信息，放入数据到excel
        /*根据模版单元格数据 与 数据库字段匹配 ，匹配得上就替换值*/
        User user = userService.getBaseMapper().selectById(id);
        System.out.println(user);
        //遍历模版，填充数据
        Workbook workbook1 = ExcelExportUtils.writeToExcel(user, workbook, path.getPath() + user.getPhoto());
        //删除设置内容样式的工作表
        workbook1.removeSheetAt(1);
        //导出的文件名称
        String fileName = "产品数据.xlsx";
        response.setHeader( "Content-Disposition", "attachment;filename="  + new String(fileName.getBytes(),"ISO8859-1"));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook1.write(response.getOutputStream());
    }
}
